﻿

using DataCore.XML;
using DataDriven;
using DataService.Extensions;
using DataService.IService;

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using System.Diagnostics;
using System.Collections.Concurrent;
using System.Threading;
using DataService.Core.Extensions;
using System.Reflection;


using DataService.Core.Common;
using DataService.Core.Reflection;
using DataService.Cache.IService;
using DataService.Cache.Caching;
using DataService.Cache.Data;
using DataService.Core.DataHelper;
using DataService.Core.ModeCode;
using DataService.Core.ServiceDTO;
using DataService.DataAccessLayer;
using DataService.Core.Collections;

namespace DataService.Common
{
    public abstract class RemoteDB : IRemoteDB
    {
        protected virtual string ParameterPrefix => "@";
        /// <summary>链接字符串</summary>
        public String ConnectionString { get; set; }
        /// <summary>模型文件路径</summary>
        public virtual String ModeFilePath { get; set; }
        /// <summary>
        /// Key: 表hash；Value:主键字段
        /// </summary>
        private static ConcurrentDictionary<int, List<string>> _dbPks = new ConcurrentDictionary<int, List<string>>();
        public abstract DatabaseType DBType { get; }
        private static List<ModelColumns> _modelColumns = new List<ModelColumns>();
        private static List<ModelIndexs> _modelIndexs = new List<ModelIndexs>();
        private static List<ModelColumns> _sourceModelColumns = new List<ModelColumns>();
        private static List<ModelIndexs> _sourceModelIndexs = new List<ModelIndexs>();

        protected static ConcurrentDictionary<int, List<string>> _tablesFields = new ConcurrentDictionary<int, List<string>>();

        private static readonly object _lock = new object();
        private int _lockTimeOut = 5 * 1000;

        private static readonly object _modelLock = new object();

        #region 分页
        public int PageTakeNum = 10000;
        #endregion
        private static ICache _requestPageCache = DictionaryCache.Instance;
        protected Stopwatch _stopWatch;
        #region 获取模型表结构
        protected List<ModelColumns> GetSourceModelColumns()
        {
            if (_sourceModelColumns != null && _sourceModelColumns.Count > 0)
            {
                return _sourceModelColumns;
            }
            string filePath = ModeFilePath;
            string connectionString = string.Format("Data Source={0};", filePath);

            using (DatabaseProvider databaseProvider = new DataService.DataAccessLayer.SQLiteProvider())
            {
                databaseProvider.ConnectionString = connectionString;
                object paramValue = null;
                string sql = DBSchemaSqlExtensions.GetSelectSql<ModelColumns>(DBType, null, ref paramValue, typeof(ModelColumns).Name, null);
                List<ModelColumns> modelColumns = MapperService.QueryInternal<ModelColumns>(databaseProvider.DbConnection, sql, paramValue, null, null, null, ParameterPrefix).ToList();
                _sourceModelColumns = modelColumns;
                Console.WriteLine($"从模型数据库读取 表字段信息");
                return modelColumns;
            }
        }
        public virtual List<string> GetModelPkFields<T>()
        {
            int hashCode = typeof(T).GetHashCode();
            if (_dbPks.ContainsKey(hashCode))
            {
                return _dbPks[hashCode];
            }
            else
            {
                lock (_modelLock)
                {
                    if (_dbPks.ContainsKey(hashCode))
                    {
                        return _dbPks[hashCode];
                    }
                    string modelTableName = typeof(T).Name;

                    var indexDatas = MakeSourceIndexs();
                    List<ModelIndexs> modelIndexs = indexDatas.Where(x => x.TableName == modelTableName && x.PrimaryKey).ToList();
                    var pkFields = modelIndexs.Select(x => x.ColumnName).ToList();
                    if (pkFields == null || pkFields.Count == 0)
                    {
                        pkFields = new List<string>();
                        //通过属性获取主键
                        var propertyInfos = typeof(T).GetProperties();
                        foreach (var itemPropertyInfo in propertyInfos)
                        {
                            string fieldIndex = AttributeHelper.GetCustomAttributeValue<FieldndexAttribute, string>(itemPropertyInfo, false);
                            if (fieldIndex == "PrimaryKey")
                            {
                                pkFields.Add(itemPropertyInfo.Name);
                            }
                        }
                         DataService.Core.Log.TraceLog.WriteLine($"从实体类（{modelTableName}）,读取主键：{string.Join(",", pkFields)}", "字段");
                    }
                    _dbPks[hashCode] = pkFields;
                }
                return _dbPks[hashCode];
            }
        }

        protected List<ModelIndexs> MakeSourceIndexs()
        {
            if (_sourceModelIndexs != null && _sourceModelIndexs.Count > 0)
            {
                return _sourceModelIndexs;
            }
            string filePath = ModeFilePath;

            string connectionString = string.Format("Data Source={0};", filePath);

            using (DatabaseProvider databaseProvider = new DataService.DataAccessLayer.SQLiteProvider())
            {
                databaseProvider.ConnectionString = connectionString;
                object paramValue = null;
                string sql = DBSchemaSqlExtensions.GetSelectSql<ModelIndexs>(DBType, null, ref paramValue, typeof(ModelIndexs).Name, null);
                List<ModelIndexs> modelIndexs = MapperService.QueryInternal<ModelIndexs>(databaseProvider.DbConnection, sql, paramValue, null, null, null, ParameterPrefix).ToList();
                _sourceModelIndexs = modelIndexs;
                return modelIndexs;
            }
        }


        #endregion

        #region 添加/修改/删除
        public virtual int Insert<T>(T t, string suffix, DbConnection conn, DbTransaction tran)
        {
            string sql = DBSchemaSqlExtensions.GetInsertSql<T>(DBType, typeof(T).Name + suffix);
            return MapperService.ExecuteInternal(conn, sql, t, tran, null, null, ParameterPrefix);
        }
        public virtual int Insert<T>(IList<T> t, string suffix, DbConnection conn, DbTransaction tran)
        {
            if (t == null || t.Count == 0)
            {
                return 0;
            }
            string sql = DBSchemaSqlExtensions.GetInsertSql<T>(DBType, typeof(T).Name + suffix);
            return MapperService.ExecuteInternal(conn, sql, t, tran, null, null, ParameterPrefix);
        }

        /// <summary>
        /// 快速批量插入
        /// </summary>
        public virtual int BeatchInsert<T>(IList<T> dataList, string suffix, DbConnection conn, DbTransaction tran)
        {
            if (dataList == null || dataList.Count == 0)
            {
                return 0;
            }
            #region 批量插入
            int totalCount = 0;

            string fullTableName = typeof(T).Name + suffix;
            Type type = typeof(T);

            PropertyInfo[] fields = type.GetProperties();
            string data_Fields = string.Join(",", fields.Select(s => s.Name));
            Dictionary<string, Type> fieldDic = new Dictionary<string, Type>();
            foreach (PropertyInfo itemPropertyInfo in fields)
            {
                fieldDic[itemPropertyInfo.Name] = itemPropertyInfo.PropertyType;
            }
            //5000条一个批次，
            var dic = BatchHelper.ReturnBeatch<T>(dataList, 5000);
            List<string> insertSqls = new List<string>();
            foreach (var item in dic)
            {
                _stopWatch = System.Diagnostics.Stopwatch.StartNew();
                DataTable dataTable = DataTableHelper.ListToTable(item.Value);
                _stopWatch.Stop();
                 DataService.Core.Log.TraceLog.WriteLine($"转换DataTable，转换量：{item.Value.Count}条，耗时：{_stopWatch.ElapsedMilliseconds}毫秒", $"{DBType.ToString()}_快速批量插入");

                _stopWatch = System.Diagnostics.Stopwatch.StartNew();
                string beatchInsertSql = GetBeatchInsertSql(dataTable, fieldDic, fullTableName, data_Fields);
                insertSqls.Add(beatchInsertSql);
                _stopWatch.Stop();
                 DataService.Core.Log.TraceLog.WriteLine($"构建语句耗时：{_stopWatch.ElapsedMilliseconds}毫秒", $"{DBType.ToString()}_快速批量插入");

            }
            _stopWatch = System.Diagnostics.Stopwatch.StartNew();

            totalCount += MapperService.ExecuteSchemaSql(conn, tran, insertSqls, ParameterPrefix);
            _stopWatch.Stop();
             DataService.Core.Log.TraceLog.WriteLine($"快速批量插入，执行：{dataList.Count}次，耗时：{_stopWatch.ElapsedMilliseconds}毫秒", $"{DBType.ToString()}_快速批量插入");
            return totalCount;
            #endregion
        }
        /// <summary>
        /// 构建快速批量插入sql
        /// </summary>
        public string GetBeatchInsertSql(DataTable dataTable, Dictionary<string, Type> fieldTypeDic, string fullTableName, string data_Fields)
        {

            //优化时，使用多处理器，多线程
            StringBuilder stringBuilder = PoolFactory.StringBuilder.Get();
            stringBuilder.Append($"INSERT INTO {fullTableName}({data_Fields}) VALUES");
            foreach (DataRow itemRow in dataTable.Rows)
            {
                stringBuilder.Append("(");
                foreach (var itemPair in fieldTypeDic)
                {
                    object val = itemRow[itemPair.Key];
                    Type fieldType = itemPair.Value;
                    bool isNull = val == DBNull.Value || val == null;
                    if (fieldType == typeof(String))
                    {
                        if (isNull)
                        {
                            stringBuilder.Append("''").Append(",");
                        }
                        else
                        {
                            stringBuilder.AppendFormat("'{0}'", val).Append(",");
                        }
                    }
                    else if (fieldType == typeof(DateTime) || fieldType == typeof(DateTime?) || fieldType == typeof(Guid) || fieldType == typeof(Guid?))
                    {
                        //datetime和Guid 通常被作为索引字段,禁止为null，防止意外出错，这里做null判断
                        if (isNull)
                        {
                            stringBuilder.Append("null").Append(",");
                        }
                        else
                        {
                            stringBuilder.AppendFormat("'{0}'", val).Append(",");
                        }
                    }
                    else if (fieldType == typeof(Boolean))
                    {
                        bool tempVal = val.ToBoolean();
                        int paramValu = tempVal ? 1 : 0;
                        stringBuilder.AppendFormat("{0}", paramValu).Append(",");
                    }
                    else
                    {
                        if (isNull)
                        {
                            stringBuilder.Append("null").Append(",");
                        }
                        else
                        {
                            stringBuilder.AppendFormat("{0}", val).Append(",");
                        }


                    }
                }
                stringBuilder.Length--;
                stringBuilder.Append(")").Append(",");
            }
            stringBuilder.Length--;
            string insertSql = stringBuilder.Put(true);
            return insertSql;
        }


        public virtual int Update<T>(T t, string suffix, DbConnection conn, DbTransaction tran)
        {
            List<string> pkFields = GetModelPkFields<T>();

            string sql = DBSchemaSqlExtensions.GetUpdateSql<T>(DBType, pkFields, typeof(T).Name + suffix);
            return MapperService.ExecuteInternal(conn, sql, t, tran, null, null, ParameterPrefix);
        }


        public virtual int Update<T>(T t, Expression<Func<T, object>> fieldFunc, string suffix, DbConnection conn, DbTransaction tran)
        {
            List<string> pkFields = GetModelPkFields<T>();
            string sql = DBSchemaSqlExtensions.GetUpdateSql<T>(DBType, pkFields, fieldFunc, typeof(T).Name + suffix);
            return MapperService.ExecuteInternal(conn, sql, t, tran, null, null, ParameterPrefix);
        }


        public virtual int BeatchUpdate<T>(IEnumerable<T> t, string suffix, DbConnection conn, DbTransaction tran)
        {
            if (t == null || t.Count() == 0)
            {
                return 0;
            }
            List<string> pkFields = GetModelPkFields<T>();

            string sql = DBSchemaSqlExtensions.GetUpdateSql<T>(DBType, pkFields, typeof(T).Name + suffix);
            return MapperService.ExecuteInternal(conn, sql, t, tran, null, null, ParameterPrefix);
        }
        public virtual int BeatchUpdate<T>(IEnumerable<T> t, Expression<Func<T, object>> fieldFunc, string suffix, DbConnection conn, DbTransaction tran)
        {
            List<string> pkFields = GetModelPkFields<T>();

            string sql = DBSchemaSqlExtensions.GetUpdateSql<T>(DBType, pkFields, fieldFunc, typeof(T).Name + suffix);
            return MapperService.ExecuteInternal(conn, sql, t, tran, null, null, ParameterPrefix);
        }

        public virtual int Delete<T>(Expression<Func<T, bool>> whereFunc, string suffix, DbConnection conn, DbTransaction tran)
        {
            List<string> tableNames = new List<string>();
            if (!string.IsNullOrEmpty(suffix))
            {
                tableNames.Add(typeof(T).Name + suffix);
            }
            else
            {
                tableNames = FindAllTableNames(conn, tran, typeof(T).Name);
            }
            int deltotalCount = 0;
            foreach (string itemTableName in tableNames)
            {
                object paramValue = null;
                string sql = DBSchemaSqlExtensions.GetDeleteSql<T>(DBType, whereFunc, ref paramValue, itemTableName);
                deltotalCount += MapperService.ExecuteInternal(conn, sql, paramValue, tran, null, null, ParameterPrefix);
            }
            return deltotalCount;
        }

        #endregion




        public virtual int ScalarCount<T>(Expression<Func<T, bool>> whereFunc, string suffix, DbConnection cnn, DbTransaction tran, int? commandTimeout, CommandType? commandType)
        {

            List<string> tableNames = new List<string>();
            if (!string.IsNullOrEmpty(suffix))
            {
                tableNames.Add(typeof(T).Name + suffix);
            }
            else
            {
                tableNames = FindAllTableNames(cnn, tran, typeof(T).Name);
            }
            int totalCount = 0;
            foreach (string itemTaleName in tableNames)
            {
                object paramValue = null;
                string sql = DBSchemaSqlExtensions.GetScalarCountSql<T>(DBType, whereFunc, ref paramValue, itemTaleName, cnn.Database);
                totalCount += MapperService.ScalarInternal<int>(cnn, sql, paramValue, tran, commandTimeout, commandType, ParameterPrefix);
            }
            return totalCount;
        }
        public virtual DataTable QueryDataTable(string sql, Dictionary<string, object> dic, DbConnection conn, DbTransaction tran)
        {
            return MapperService.QueryDataTable(conn, sql, dic, tran, null, null, ParameterPrefix);
        }
        public virtual IEnumerable<T> Query<T>(Expression<Func<T, bool>> whereFunc, string suffix, DbConnection cnn, DbTransaction tran, int? commandTimeout, Expression<Func<T, object>> fieldFunc)
        {
            ////使用索引idx1查询
            //explain select * from t1 where c2 = 130;  //当数据量很大时
            //explain select * from t1 index idx1 where c2 = 130;  //或直接指定全局索引
            //如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
            // select id from t where num = @num
            //可以改为强制查询使用索引：
            //select id from t with(index(索引名)) where num=@num
            List<T> resultList = new List<T>();
            List<string> tableNames = new List<string>();
            //优化阶段使用缓存取表名
            if (!string.IsNullOrEmpty(suffix))
            {
                tableNames.Add(typeof(T).Name + suffix);
            }
            else
            {
                tableNames = FindAllTableNames(cnn, tran, typeof(T).Name);
            }
            foreach (string itemTaleName in tableNames)
            {
                object paramValue = null;
                string sql = DBSchemaSqlExtensions.GetSelectSql<T>(DBType, whereFunc, ref paramValue, itemTaleName, fieldFunc);
                var tempList = MapperService.QueryInternal<T>(cnn, sql, paramValue, tran, commandTimeout, null, ParameterPrefix);
                resultList.AddRange(tempList.ToList());
            }
            return resultList;
        }

        public virtual async Task<IEnumerable<T>> QueryAsync<T>(Expression<Func<T, bool>> whereFunc, string suffix, DbConnection cnn, DbTransaction tran, int? commandTimeout, Expression<Func<T, object>> fieldFunc)
        {
            ////使用索引idx1查询
            //explain select * from t1 where c2 = 130;  //当数据量很大时
            //explain select * from t1 index idx1 where c2 = 130;  //或直接指定全局索引
            var task = await Task.Factory.StartNew(() =>
            {
                List<T> resultList = new List<T>();
                List<string> tableNames = new List<string>();
                if (!string.IsNullOrEmpty(suffix))
                {
                    tableNames.Add(typeof(T).Name + suffix);
                }
                else
                {
                    tableNames = FindAllTableNames(cnn, tran, typeof(T).Name);
                }
                foreach (string itemTaleName in tableNames)
                {
                    object paramValue = null;
                    string sql = DBSchemaSqlExtensions.GetSelectSql<T>(DBType, whereFunc, ref paramValue, itemTaleName, fieldFunc);
                    var tempList = MapperService.QueryInternal<T>(cnn, sql, paramValue, tran, commandTimeout, null, ParameterPrefix);
                    resultList.AddRange(tempList.ToList());
                }
                return resultList;
            });
            return task;
        }
        public virtual T QueryModel<T>(Expression<Func<T, bool>> whereFunc, string suffix, DbConnection cnn, DbTransaction tran, int? commandTimeout, Expression<Func<T, object>> fieldFunc)
        {
            ////使用索引idx1查询
            //explain select * from t1 where c2 = 130;  //当数据量很大时
            //explain select * from t1 index idx1 where c2 = 130;  //或直接指定全局索引
            List<string> tableNames = new List<string>();
            if (!string.IsNullOrEmpty(suffix))
            {
                tableNames.Add(typeof(T).Name + suffix);
            }
            else
            {
                tableNames = FindAllTableNames(cnn, tran, typeof(T).Name);
            }
            foreach (string itemTableName in tableNames)
            {
                object paramValue = null;
                string sql = DBSchemaSqlExtensions.GetSelectSql<T>(DBType, whereFunc, ref paramValue, itemTableName, fieldFunc);
                var resultModel = MapperService.QueryModel<T>(cnn, sql, paramValue, tran, commandTimeout, null, ParameterPrefix);
                if (resultModel != null)
                {
                    return resultModel;
                }
            }
            return default(T);
        }
        /// <summary>
        /// 前序列必须使用等于或者in操作最右边的列可以使用不等式，这样索引才可以完全生效。
        ///同时，where子句中的列不需要全建立了索引，但是必须保证建立索引的列之间没有间隙
        /// 前序列必须使用等于或者in操作最右边的列可以使用不等式，这样索引才可以完全生效。
        /// WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello'
        /// 那这里只有a，b和c的索引会是有效的，d列的索引会失效，因为它在c列的右边，而c列使用
        /// 了不等式，根据使用不等式的限制，c列已经属于最右边。
        /// WHERE b=5 AND c=3 AND d =  'hello'
        ///不生效,没有按照索引顺序
        ///between:
        ///对于between，or，like，都无法使用索引。必须要用对应的等式来生效索引
        // WHERE scope BETWEEN 100 and 202;这时就应该将其转换成：WHERE scope >= 10 AND scope <= 20;
        ///or(用in):WHERE scope = 'abc' OR scope = 'xyz';此时应该将它转换成：WHERE scope IN('abc', 'xyz');
        /// </summary>
        public virtual IEnumerable<T> QueryPage<T>(Expression<Func<T, bool>> whereFunc, RequestPageDTO requestPageDTO, string suffix, DbConnection cnn, DbTransaction tran, int? commandTimeout, Expression<Func<T, object>> fieldFunc, ref ResultPageDTO resultPageDTO)
        {
            ////使用索引idx1查询
            //explain select * from t1 where c2 = 130;  //当数据量很大时
            //explain select * from t1 index idx1 where c2 = 130;  //或直接指定全局索引
            //海量数据查询分页，耗时较长的是【查找第一页的开始位置】
            // UniqueKey为空：说明UniqueKey参数为空,只有第一次请求（默认页码为1）是为空；若不是第一次请求分页则 UniqueKey不能为空
            if (string.IsNullOrEmpty(requestPageDTO.UniqueKey) && requestPageDTO.PageIndex != 1)
            {
                throw new ArgumentException("请求唯一标识不能为空！");
            }
            //只有第一页或者搜索条件改变时 ParamNextStartId和ParamUpStartId为0
            if ((requestPageDTO.ParamNextStartId == 0 || requestPageDTO.ParamUpStartId == 0) && requestPageDTO.PageIndex != 1)
            {
                return new List<T>();
            }

            List<string> sourcesTableNames = new List<string>();
            if (!string.IsNullOrEmpty(suffix))
            {
                sourcesTableNames.Add(typeof(T).Name + suffix);
            }
            else
            {
                sourcesTableNames = FindAllTableNames(cnn, tran, typeof(T).Name);
            }
            //分页根据数据创建时间排序
            string cacheKey = string.Empty;
            //1.处理页码和开始位置、页码缓存
            int previousPageIndex = SetPageCacheValue<T>(requestPageDTO, ref resultPageDTO, ref cacheKey);
            #region  获取主键
            List<string> pkFields = GetModelPkFields<T>();
            #endregion

            #region 是否是第一页
            long requestNextStartId = requestPageDTO.ParamNextStartId;
            //搜索条件改变 或者 第一次请求分页；此时页码应该为1；在进入该方法前进行判断 页码 问题
            //查找起始位置
            if (requestNextStartId == 0 || previousPageIndex == 0)
            {
                ResolveExpress resolve = new ResolveExpress();
                var dic = resolve.ResolveExpression<T>(whereFunc, ParameterPrefix);
                object paramValue = dic["values"];
                string whereSql = dic["sql"].ToString();
                string field = pkFields[0];//Id 字段
                                           //  string sql = $" select {field} from ## where {whereSql} Order By {field} asc limit 1";
                long resultStartRow = 0;
                foreach (var itemTableName in sourcesTableNames)
                {
                    resultStartRow = FindStratRow<T>(whereSql, paramValue, itemTableName, pkFields, cnn, tran);
                    if (resultStartRow > 0)
                    {
                        break;
                    }
                }
                requestNextStartId = resultStartRow;
                if (requestNextStartId == 0)
                {
                    //没有匹配到开始位置,即没有需要展示的数据
                    return new List<T>();
                }
                requestPageDTO.ParamNextStartId = requestNextStartId;
            }
            #endregion

            List<T> resultList = new List<T>();
            if (previousPageIndex == 0)
            {
                //默认请求第一页数据；页码等于1
                //返回第一页数据
                int takeNum = 0;
                List<string> newTableNames = GetBeforeTables<T>(requestNextStartId, sourcesTableNames);
                resultList = FindAllForward<T>(whereFunc, requestPageDTO, newTableNames, takeNum, cnn, tran, commandTimeout, fieldFunc, pkFields, ref resultPageDTO);
            }
            if (previousPageIndex > 0)
            {
                int diffNum = requestPageDTO.PageIndex - previousPageIndex;
                if (diffNum > 0)
                {
                    List<string> newTableNames = GetBeforeTables<T>(requestNextStartId, sourcesTableNames);
                    //本次页码大于上次页码；正向分页
                    int takeNum = (diffNum - 1) * requestPageDTO.PageSize;
                    resultList = FindAllForward<T>(whereFunc, requestPageDTO, newTableNames, takeNum, cnn, tran, commandTimeout, fieldFunc, pkFields, ref resultPageDTO);
                }
                if (diffNum == 0)
                {
                }
                if (diffNum < 0)
                {
                    //本次 页码小于上次页码；反向分页
                    int tempDiff = Math.Abs(diffNum);
                    int takeNum = (tempDiff - 1) * requestPageDTO.PageSize;
                    resultList = FindAllreverse<T>(whereFunc, requestPageDTO, sourcesTableNames, takeNum, cnn, tran, commandTimeout, fieldFunc, pkFields, ref resultPageDTO);
                    //因为反向页码查找，所以此处需要再旋转一次
                    resultList.Reverse();
                }
            }
            //3.更新 缓存值 
            SetCurrentPageCache(cacheKey, requestPageDTO.PageIndex);
            #region 查询总记录数
            int totalCount = FindAllRows(sourcesTableNames, cnn, tran, requestPageDTO.Schema);
            resultPageDTO.TotalCount = totalCount;
            #endregion
            #region 查询通过条件筛选的记录数

            #endregion
            return resultList;
        }
        #region 正向分页，预处理表
        protected List<string> GetBeforeTables<T>(long requestNextStartId, List<string> sourceTableNames)
        {
            string currentTableName = DataBaseHelper.GetTableNameById<T>(requestNextStartId);
            if (!sourceTableNames.Any(x => x.ToLower() == currentTableName.ToLower()))
            {
                throw new ArgumentException("无法匹配到表中记录");
            }
            List<string> dataTableNames = new List<string>();
            bool isFind = false;
            foreach (var itemTableName in sourceTableNames)
            {
                if (isFind)
                {
                    dataTableNames.Add(itemTableName);
                }
                if (itemTableName.ToLower() == currentTableName.ToLower())
                {
                    dataTableNames.Add(itemTableName);
                    isFind = true;
                }
            }
            return dataTableNames;
        }
        #endregion

        #region 查询分页
        private string GetUniqueKey()
        {
            string uniqueKey = Guid.NewGuid().ToString();
            uniqueKey = uniqueKey.Replace("-", "");
            return uniqueKey;
        }
        /// <summary>
        /// 设置分页页码缓存；将上次 页码 保存至缓存
        /// </summary>
        /// <returns>页码值</returns>
        /// <exception cref="ArgumentNullException">缓存不存在请求标识</exception>
        private int SetPageCacheValue<T>(RequestPageDTO requestPageDTO, ref ResultPageDTO resultPageDTO, ref string cacheKey)
        {
            //第一次点击分页的时候，默认页码为1
            int previousPageIndex = 0;//上次的页码
            string uniqueKey = requestPageDTO.UniqueKey;//只有第一次点击分页的时候，此处为空
            string prefix = "page_";

            if (string.IsNullOrEmpty(uniqueKey))
            {
                //第一次点击分页，此时默认页码为1
                int currentPageIndex = 1;//默认页码为1
                //页码保存至缓存,有效期8小时；定时任务，每天清一次缓存
                Int32 expire = 60 * 60 * 8;
                uniqueKey = GetUniqueKey();
                resultPageDTO.UniqueKey = uniqueKey;
                cacheKey = prefix + uniqueKey;
                _requestPageCache.Set<Int32>(cacheKey, currentPageIndex, expire);

                previousPageIndex = 0;
            }
            else
            {
                cacheKey = prefix + uniqueKey;
                if (!_requestPageCache.ContainsKey(cacheKey))
                {
                    throw new ArgumentNullException($"请求唯一标识错误{uniqueKey}");
                }
                //取出上次的页码
                previousPageIndex = _requestPageCache.Get<Int32>(cacheKey);
                resultPageDTO.UniqueKey = uniqueKey;
            }
            return previousPageIndex;
        }
        private void SetCurrentPageCache(string cacheKey, Int32 currentPageIndex)
        {
            Int32 expire = 60 * 60 * 8;
            _requestPageCache.Set<Int32>(cacheKey, currentPageIndex, expire);
        }
        /// <summary>
        /// 按照时间分表的分页查询
        /// 适合（首页 1 2 3 4 5 6 7 8 9 10 尾页） 页码展示；
        /// 正向分页
        /// </summary>
        /// <param name="takeNum">需要跳过条数</param>
        /// <param name="resultPageDTO">需要返回给客户端的值</param>
        protected virtual List<T> FindAllForward<T>(Expression<Func<T, bool>> whereFunc, RequestPageDTO requestPageDTO, List<string> dataBaseTableNames, Int32 takeNum, DbConnection cnn, DbTransaction tran, int? commandTimeout, Expression<Func<T, object>> fieldFunc, List<string> pkFields, ref ResultPageDTO resultPageDTO)
        {
            //海量数据瓶颈是在数据库，减少数据库读写，减少数据库处理逻辑，宁可把数据多读出来放到内存处理，也要减少数据库等待时间
            //思路：先分页，再查询总记录数
            //7.执行到这里两种情况 1.第一页 2.页码大于等于2且UniqueKey不为空
            string fieldIdName = pkFields[0];
            long requestNextStartId = requestPageDTO.ParamNextStartId;

            #region 解析查询条件
            ResolveExpress resolve = new ResolveExpress();
            var dic = resolve.ResolveExpression<T>(whereFunc, ParameterPrefix);
            object paramValue = dic["values"];
            string whereSql = dic["sql"].ToString();

            string sql = DBSchemaSqlExtensions.GetPageSelectSql<T>(DBType, whereFunc, ref paramValue, "##", fieldFunc);
            #endregion

            //需要查询总条数
            int dataNum = takeNum + requestPageDTO.PageSize;
             DataService.Core.Log.TraceLog.WriteLine($"线程：{Thread.CurrentThread.ManagedThreadId}，正向分页，第{requestPageDTO.PageIndex}页，需要跳过条数：{takeNum}", $"{DBType.ToString()}_分页查询");
            List<T> resultList = new List<T>();
            //6.遍历所有表结构,表必须是根据表名升序排列

            foreach (string itemTableName in dataBaseTableNames)
            {
                //优化时，可以先解析出 PreviousMaxId 的时间,再解析出 itemTableName的时间，只循环大于 PreviousMaxId的表
                //1.先查询出最大Id

                long startId = FindStratRow<T>(whereSql, paramValue, itemTableName, pkFields, cnn, tran);
                if (startId == 0)
                {
                    continue;
                }
                long endId = FindEndRow<T>(whereSql, paramValue, itemTableName, pkFields, cnn, tran);
                endId = endId + 1;
                //因为查找数据用的是大于，起始位置 减1，所以 判断 数据是否在该表区间要加1
                //加1后为实际在该表中的位置
                long tempVal = requestNextStartId + 1;
                bool isFlag = tempVal >= (startId + 1) && tempVal <= endId;
                if (!isFlag)
                {
                    requestNextStartId = startId;
                }
                //2.先查询出select * from T
                string firstSql = sql.Replace("##", itemTableName);
                #region 第一种查询方式
                //8.海量数据查询,瓶颈在数据库，优化阶段，把热点数据放到缓存里面 或者把数据查询到一万以内再通过where条件筛选
                //string doSql = $"{firstSql} where {fieldIdName}>{requestNextStartId} and {whereSql} order by {fieldIdName} asc limit {dataNum}";
                string doSql = SetForwardPageSql(firstSql, whereSql, fieldIdName, requestNextStartId, dataNum);
                _stopWatch = Stopwatch.StartNew();
                var queryData = MapperService.QueryInternal<T>(cnn, doSql, paramValue, tran, commandTimeout, null, ParameterPrefix);
                _stopWatch.Stop();
                 DataService.Core.Log.TraceLog.WriteLine($"正向分页={doSql}；耗时：{_stopWatch.ElapsedMilliseconds}毫秒", $"{DBType.ToString()}_分页查询");
                resultList.AddRange(queryData?.ToList());
                #endregion

                #region 第二种查询方式，适合数据比较集中的表或者日志型的表
                ////3.缩小查询范围
                //while (requestNextStartId < endId && requestNextStartId > 0)
                //{
                //    if (requestNextStartId == endId - 1)
                //    {
                //        break;
                //    }
                //    //8.海量数据查询,瓶颈在数据库，优化阶段，数据库不做子查询，先查询出1万条数据在程序中根据条件筛选
                //    string doSql = $"{firstSql} where {fieldIdName}>{requestNextStartId} order by {fieldIdName} asc limit {PageTakeNum}";
                //    string secondSql = $"select * from ({doSql}) WHERE {whereSql} limit {dataNum}";
                //     DataService.Core.Log.TraceLog.WriteLine($"正向分页={secondSql}",$"{DBType.ToString()}_分页查询");
                //    var queryData = MapperService.QueryInternal<T>(cnn, secondSql, paramValue, tran, commandTimeout, null);
                //    resultList.AddRange(queryData?.ToList());
                //    //此处正在查询范围内数据，所以需要判断dataNum；不能判断takeNum
                //    if (resultList.Count >= dataNum)
                //    {
                //        //结束单表查找
                //         DataService.Core.Log.TraceLog.WriteLine($"线程：{Thread.CurrentThread.ManagedThreadId}，正向分页,已经凑够一页数据【{itemTableName}】", $"{DBType.ToString()}_分页查询");
                //        break;
                //    }
                //    //4.当前批次最大值Id
                //    string thirdSql = $"select max({fieldIdName}) from (select {fieldIdName} from {itemTableName} where {fieldIdName}>{requestNextStartId} order by {fieldIdName} asc limit {PageTakeNum})";
                //    requestNextStartId = MapperService.ScalarInternal<long>(cnn, thirdSql, null, tran, commandTimeout, null);
                //}
                #endregion

                //取出来的条数可能小于dataNum?
                if (resultList.Count >= dataNum)
                {
                    //结束遍历表查找
                    break;
                }
            }
            if (resultList.Count >= takeNum)
            {
                resultList = resultList.Skip(takeNum).Take(requestPageDTO.PageSize).ToList();
                 DataService.Core.Log.TraceLog.WriteLine($"线程：{Thread.CurrentThread.ManagedThreadId}，正向分页,跳过{takeNum}条,取{requestPageDTO.PageSize}条", $"{DBType.ToString()}_分页查询");
                //5.查询出当前页最大Id和最小Id
                long paramMaxId = FieldHelper.GetMaxValue(resultList, fieldIdName);
                long paramMinId = FieldHelper.GetMinValue(resultList, fieldIdName);
                resultPageDTO.ParamNextStartId = paramMaxId;
                resultPageDTO.ParamUpStartId = paramMinId;
            }
            else
            {
                //6.如果所有表凑不够一页数据
                //在DAl层，校验页码不能大于最大页码，所以此处不处理，如果无法精确统计pageIndex最大值,则此处需要对不足一页的数据做处理
                //没有凑够一页；说明当前pageIndex没有数据；暂时先赋值为
                resultList = new List<T>();
            }

            int recordCount = 0;

            resultPageDTO.RecordCount = recordCount;

            return resultList;

        }

        /// <summary>
        /// 适合（首页 1 2 3 4 5 6 7 8 9 10 尾页） 页码展示；
        /// 反向分页
        /// </summary>
        /// <param name="takeNum">需要跳过条数</param>
        /// <param name="resultPageDTO">需要返回给客户端的值</param>
        protected virtual List<T> FindAllreverse<T>(Expression<Func<T, bool>> whereFunc, RequestPageDTO requestPageDTO, List<string> dataBaseTableNames, Int32 takeNum, DbConnection cnn, DbTransaction tran, int? commandTimeout, Expression<Func<T, object>> fieldFunc, List<string> pkFields, ref ResultPageDTO resultPageDTO)
        {
            //优化时，将正向分页查询的所有数据缓存到内中,反向分页从内存中取数据

            //思路：先分页，再查询总记录数
            string fieldIdName = pkFields[0];
            long requestUpNumId = requestPageDTO.ParamUpStartId;

            #region 解析查询条件
            ResolveExpress resolve = new ResolveExpress();
            var dic = resolve.ResolveExpression<T>(whereFunc, ParameterPrefix);
            object paramValue = dic["values"];
            string whereSql = dic["sql"].ToString();

            string sql = DBSchemaSqlExtensions.GetPageSelectSql<T>(DBType, whereFunc, ref paramValue, "##", fieldFunc);
            #endregion
            #region 获取数据所在的表
            string fullTableName = DataBaseHelper.GetTableNameById<T>(requestUpNumId);//当前Id所在的表
            //分页思路，按照创建时间排序，创建表时间即为创建数据时间
            List<string> dataTableNames = new List<string>();
            foreach (var itemTableName in dataBaseTableNames)
            {
                dataTableNames.Add(itemTableName);
                if (itemTableName == fullTableName)
                {
                    //dataBaseTableNames 已经是按照创建时间正序排序后的结果集，反向分页取 正向分页遍历过的表即可
                    break;
                }
            }
            //反向分页，需要旋转一次表
            dataTableNames.Reverse();
            #endregion

            //需要查询总条数
            int dataNum = takeNum + requestPageDTO.PageSize;
             DataService.Core.Log.TraceLog.WriteLine($"线程：{Thread.CurrentThread.ManagedThreadId}，反向分页，第{requestPageDTO.PageIndex}页，需要跳过条数：{takeNum}", $"{DBType.ToString()}_分页查询");
            List<T> resultList = new List<T>();

            //6.遍历表
            foreach (string itemTableName in dataTableNames)
            {

                long startRow = FindStratRow<T>(whereSql, paramValue, itemTableName, pkFields, cnn, tran);//Id= 5，startRow=4
                if (startRow == 0)
                {
                    continue;
                }
                long endRow = FindEndRow<T>(whereSql, paramValue, itemTableName, pkFields, cnn, tran);//Id= 10，endRow=11
                endRow = endRow + 1;

                //因为查找数据用的是大于，起始位置 减1，所以 判断 数据是否在该表区间要加1
                //加1后为实际在该表中的位置
                long tempVal = requestUpNumId - 1;
                bool isFlag = tempVal >= (startRow + 1) && tempVal <= (endRow - 1);
                if (!isFlag)
                {
                    requestUpNumId = endRow;
                }
                //2.先查询出select * from T
                string firstSql = sql.Replace("##", itemTableName);
                #region 第一种查询方式
                _stopWatch = Stopwatch.StartNew();
                //8.海量数据查询,瓶颈在数据库，优化阶段，热点数据放到缓存中
                // string doSql = $"{firstSql} where {fieldIdName}<{requestUpNumId} and {whereSql} order by {fieldIdName} desc limit {dataNum}";
                string doSql = SetReversePageSql(firstSql, whereSql, fieldIdName, requestUpNumId, dataNum);

                var queryData = MapperService.QueryInternal<T>(cnn, doSql, paramValue, tran, commandTimeout, null, ParameterPrefix);
                _stopWatch.Stop();
                 DataService.Core.Log.TraceLog.WriteLine($"反向分页={doSql}；耗时：{_stopWatch.ElapsedMilliseconds}毫秒", $"{DBType.ToString()}_分页查询");
                resultList.AddRange(queryData?.ToList());
                #endregion
                #region 第二种查询方式，适合数据比较集中的表
                ////3.缩小查询范围
                //while (requestUpNumId > startRow && requestUpNumId > 0)
                //{
                //    if (requestUpNumId == (startRow + 1))
                //    {
                //        break;
                //    }
                //    //8.海量数据查询,瓶颈在数据库，优化阶段，数据库不做子查询，先查询出1万条数据在程序中根据条件筛选
                //    //3.1 查询出距离 requestUpNumId 最新的一万条数据；根据Id倒排序
                //    string doSql = $"{firstSql} where {fieldIdName}<{requestUpNumId} order by {fieldIdName} desc limit {PageTakeNum}";
                //    string secondSql = $"select * from ({doSql}) WHERE {whereSql} limit {dataNum}";
                //     DataService.Core.Log.TraceLog.WriteLine($"反向分页={secondSql}",$"{DBType.ToString()}_分页查询");
                //    var queryData = MapperService.QueryInternal<T>(cnn, secondSql, paramValue, tran, commandTimeout, null);
                //    resultList.AddRange(queryData?.ToList());
                //    if (resultList.Count >= dataNum)
                //    {
                //        //结束单表查找
                //         DataService.Core.Log.TraceLog.WriteLine($"线程：{Thread.CurrentThread.ManagedThreadId}，反向分页,已经凑够一页数据【{itemTableName}】",$"{DBType.ToString()}_分页查询");
                //        break;
                //    }
                //    //4.查询出当前批次的最小Id
                //    string thirdSql = $"select min({fieldIdName}) from (select {fieldIdName} from {itemTableName} where {fieldIdName}<{requestUpNumId} order by {fieldIdName} desc limit {PageTakeNum} )";
                //    requestUpNumId = MapperService.ScalarInternal<long>(cnn, thirdSql, null, tran, commandTimeout, null);
                //}
                #endregion

                //取出来的条数可能小于dataNum?
                if (resultList.Count >= dataNum)
                {
                    //结束遍历表查找
                    break;
                }
            }
            if (resultList.Count >= takeNum)
            {
                resultList = resultList.Skip(takeNum).Take(requestPageDTO.PageSize).ToList();
                 DataService.Core.Log.TraceLog.WriteLine($"线程：{Thread.CurrentThread.ManagedThreadId}，反向分页,跳过{takeNum}条,取{requestPageDTO.PageSize}条", $"{DBType.ToString()}_分页查询");
                //5.查询出最大Id和最小Id
                long paramMaxId = FieldHelper.GetMaxValue(resultList, fieldIdName);
                long paramMinId = FieldHelper.GetMinValue(resultList, fieldIdName);

                resultPageDTO.ParamNextStartId = paramMaxId;
                resultPageDTO.ParamUpStartId = paramMinId;

            }
            else
            {
                //6.如果所有表凑不够一页数据
                //在DAl层，校验页码不能大于最大页码，所以此处不处理，如果无法精确统计pageIndex最大值,则此处需要对不足一页的数据做处理
                //没有凑够一页；说明当前pageIndex没有数据；暂时先赋值为
                resultList = new List<T>();
            }
            int recordCount = 0;
            resultPageDTO.RecordCount = recordCount;
            return resultList;

        }


        protected virtual string SetForwardPageSql(string sql, string where, string pkIdName, long pkValue, int dataNum)
        {
            //若不传参照ID（即pkValue）,则可以使用,以为 取第100页为例；每页20条
            //int pageSkip=(100-1)*20
            // string doSql="select* from T where条件 and Id >= (select Id from T wher条件 limit pageSkip,1) limit 20;";

            string doSql = $"{sql} where {pkIdName}>{pkValue} and {where} order by {pkIdName} asc limit {dataNum}";
            return doSql;
        }

        protected virtual string SetReversePageSql(string sql, string where, string pkIdName, long pkValue, int dataNum)
        {
            string doSql = $"{sql} where {pkIdName}<{pkValue} and {where} order by {pkIdName} desc limit {dataNum}";
            return doSql;
        }

        protected virtual string SetStratRowSql(string pkIdName, string tableName, string where)
        {
            string sql = $" select {pkIdName} from {tableName} where {where} Order By {pkIdName} asc limit 1";
            return sql;
        }
        protected virtual string SetEndRowSql(string pkIdName, string tableName, string where)
        {
            string sql = $" select {pkIdName} from {tableName} where {where} Order By {pkIdName} desc limit 1";
            return sql;
        }
        /// <summary>
        /// 返回查询总条数语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dataBaseName">数据库名/表空间名</param>
        /// <param name="schema">模式名</param>
        /// <returns></returns>
        protected virtual string SetTotalRowsSql(string tableName, string dataBaseName, string schema)
        {
            string sql = $"select Count(1) from {tableName}";
            return sql;
        }
        /// <summary>
        /// 分页查询最快方式
        /// 1.先定位行范围 RowId>开始行 and RowId<=结束行；开始行从0开水； select 字段 from 表 where RowId>开始行 and RowId<=结束行 and 条件
        /// 2.覆盖索引或索引 查询出主键  select Id; select * from Id>(select Id from xxxx where xxxx order by Id asc limit 1)
        /// </summary>
        /// <returns>若没有查到开始位置则返回-1，查到开始位置返回大于等于0</returns>
        /// 


        protected virtual Int64 FindStratRow<T>(string whereSql, object paramValue, string tableName, List<string> pkFields, DbConnection cnn, DbTransaction tran)
        {
            //查询最麻烦的就是 like 模糊查询；等价查询（等号）可以通过 索引方式提高查询查询效率；like 需要单独处理

            //思路：先用其他条件查询到一万以内；在一万条数据进行like，如果找不到匹配结果集则继续循环查找，直到找到结果集为止；
            // 循环 select * from (select 字段 from 表 where 其他条件) where like条件
            string field = pkFields[0];//Id 字段
            _stopWatch = Stopwatch.StartNew();

            string sql = SetStratRowSql(field, tableName, whereSql);

            Int64 resultStartRow = MapperService.ScalarInternal<long>(cnn, sql, paramValue, tran, null, null, ParameterPrefix);
            _stopWatch.Stop();
             DataService.Core.Log.TraceLog.WriteLine($"线程：{Thread.CurrentThread.ManagedThreadId}，查询开始位置,执行语句：{sql}；耗时：{_stopWatch.ElapsedMilliseconds}毫秒", $"{DBType.ToString()}_查询位置");
            //因为 正向和反向分页，用的是 大于参数值，所以此处需要减1
            if (resultStartRow > 0)
            {
                //因为 正向和反向分页，用的是 大于参数值，所以此处需要减1
                resultStartRow = resultStartRow - 1;
            }
            return resultStartRow;
        }
        protected virtual Int64 FindEndRow<T>(string whereSql, object paramValue, string tableName, List<string> pkFields, DbConnection cnn, DbTransaction tran)
        {
            //查询最麻烦的就是 like 模糊查询；等价查询（等号）可以通过 索引方式提高查询查询效率；like 需要单独处理

            //思路：先用其他条件查询到一万以内；在一万条数据进行like，如果找不到匹配结果集则继续循环查找，直到找到结果集为止；
            // 循环 select * from (select 字段 from 表 where 其他条件) where like条件
            string field = pkFields[0];//Id 字段
            _stopWatch = Stopwatch.StartNew();

            string sql = SetEndRowSql(field, tableName, whereSql);
            Int64 endRow = MapperService.ScalarInternal<long>(cnn, sql, paramValue, tran, null, null, ParameterPrefix);
            _stopWatch.Stop();
             DataService.Core.Log.TraceLog.WriteLine($"线程：{Thread.CurrentThread.ManagedThreadId}，查询【结束位置】,执行语句：{sql}；耗时：{_stopWatch.ElapsedMilliseconds}毫秒", $"{DBType.ToString()}_查询位置");
            return endRow;
        }
        #endregion


        /// <summary>
        /// 查询所有分表总记录数
        /// </summary>
        public virtual Int32 FindAllRows(List<string> tableNames, DbConnection cnn, DbTransaction tran, string schema)
        {
            //sqlite所有表总条数，取的是最大值,如果中间有删除数据，会有误差，考虑到一般很少删除数据，为了速度sqlte总条数取大约值
            //sqlite取精确值，可以把sqlite删除条数更新到一个中间表中，sqlite总行数=现有大约值-已删除的条数；
            Int32 totalCount = 0;

            string dataBaseName = cnn.Database;
            foreach (var itemTableName in tableNames)
            {
                string sql = SetTotalRowsSql(itemTableName, dataBaseName, schema);
                Int32 tempCount = MapperService.ScalarInternal<Int32>(cnn, sql, null, tran, null, null, ParameterPrefix);
                totalCount += tempCount;
            }
            return totalCount;
        }
        /// <summary>
        /// 查询最大Id
        /// </summary>
        public virtual Int64 FindMaxId(string tableName, string pkFieldName, DbConnection cnn, DbTransaction tran)
        {
            //优化时，可以把最大值放到缓存中，缓存10分钟
            // string sql = $"select max({pkFieldName}) from {tableName}";
            string sql = $"select {pkFieldName} from {tableName} order by {pkFieldName} desc limit 1";
            Int64 totalCount = MapperService.ScalarInternal<Int64>(cnn, sql, null, tran, null, null, ParameterPrefix);
            return totalCount;
        }

        /// <summary>
        /// 查询最小Id
        /// </summary>
        public virtual Int64 FindMinId(string tableName, string pkFieldName, DbConnection cnn, DbTransaction tran)
        {
            //优化时，可以把最大值放到缓存中，缓存10分钟
            string sql = $"select {pkFieldName} from {tableName} order by {pkFieldName} asc limit 1";
            Int64 totalCount = MapperService.ScalarInternal<Int64>(cnn, sql, null, tran, null, null, ParameterPrefix);
            return totalCount;
        }

        /// <summary>
        /// 获取第一行Id
        /// </summary>
        public virtual Int64 FindFirstRowId<T>(string tableName, string pkField, DbConnection cnn, DbTransaction tran)
        {
            string sql = $"select {pkField} from {tableName} limit 0,1";
            Int64 firstIdVal = MapperService.ScalarInternal<Int64>(cnn, sql, null, tran, null, null, ParameterPrefix);
            return firstIdVal;
        }

        //public virtual string GetPageSqlString<T>(string[] asc, string[] desc, int startNum, int maxNum, string querySql, List<string> pkFields)
        //{
        //    string orderbySql = GetOrderby<T>(asc, desc, pkFields);
        //    string sql = $"{querySql} {orderbySql} limit {startNum},{maxNum};";
        //     DataService.Core.Log.TraceLog.WriteLine($"查询分页执行语句：{sql}", "查询分页");
        //    return sql;
        //}

        public virtual string SqlAppendWhere<T>(string[] asc, string[] desc, int startRowNum, int maxNum, string querySql, List<string> pkFields, Expression<Func<T, bool>> whereFunc, ref object paramValue)
        {
            ResolveExpress resolve = new ResolveExpress();
            var dic = resolve.ResolveExpression<T>(whereFunc, ParameterPrefix);
            paramValue = dic["values"];
            string whereSql = dic["sql"].ToString();
            string orderbySql = GetOrderby<T>(asc, desc, pkFields);
            string sql = $"{querySql} WHERE {whereSql} {orderbySql} limit {startRowNum},{maxNum};";
             DataService.Core.Log.TraceLog.WriteLine($"查询分页执行语句：{sql}", "查询分页");
            return sql;
        }
        public virtual string SqlAppendWhere<T>(string[] asc, string[] desc, int maxNum, string querySql, List<string> pkFields, Expression<Func<T, bool>> whereFunc, long startId, long endId, ref object paramValue)
        {
            ResolveExpress resolve = new ResolveExpress();
            var dic = resolve.ResolveExpression<T>(whereFunc, ParameterPrefix);
            paramValue = dic["values"];
            string whereSql = dic["sql"].ToString();
            string defaultId = pkFields[0];
            string orderbySql = GetOrderby<T>(asc, desc, pkFields);
            string sql = $"{querySql} WHERE {defaultId}>{startId} and {defaultId}<={endId} and {whereSql} {orderbySql} limit {maxNum};";
             DataService.Core.Log.TraceLog.WriteLine($"查询分页执行语句：{sql}", "查询分页");
            return sql;
        }
        public virtual string GetOrderby<T>(string[] asc, string[] desc, List<string> defaultOrders)
        {
            StringBuilder stringBuilder = new StringBuilder();
            if (asc != null && asc.Length > 0)
            {
                foreach (var item in asc)
                {
                    stringBuilder.Append(item).Append(",");
                }
                stringBuilder.Length--;
                stringBuilder.Append(" asc ");
            }
            if (desc != null && desc.Length > 0)
            {
                foreach (var item in desc)
                {
                    stringBuilder.Append(item).Append(",");
                }
                stringBuilder.Length--;
                stringBuilder.Append(" desc");
            }
            if (stringBuilder.Length > 0)
            {
                return $"order by {stringBuilder.ToString()}";
            }
            string orderFields = string.Join(",", defaultOrders);
            return $"order by {orderFields}";
        }
        /// <summary>
        /// 获取所有分表表名
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <param name="modelTableName"></param>
        /// <returns>按照创建顺序，由小到大正向排序返回</returns>
        public virtual List<string> FindAllTableNames(DbConnection conn, DbTransaction tran, string modelTableName)
        {
            //优化时，需要使用缓存,当前数据库对应的表放到缓存里面
            List<string> list = FindAllTableNames(conn, tran);
            List<string> resultList = new List<string>();
            foreach (var item in list)
            {
                if (item.ToLower().StartsWith(modelTableName.ToLower()))
                {
                    resultList.Add(item);
                }
            }
            //排序
            resultList = resultList.OrderBy(x => x).ToList();

            return resultList;
        }
        /// <summary>
        /// 获取所有模型表结构
        /// </summary>
        public virtual List<string> FindAllModeTableNames(DbConnection conn, DbTransaction tran)
        {
            object paramValue = null;
            string sql = DBSchemaSqlExtensions.GetSelectSql<ModelColumns>(DBType, x => true, ref paramValue, typeof(ModelColumns).Name, t => new { t.TableName });
            var queryData = MapperService.QueryInternal<ModelColumns>(conn, sql, paramValue, tran, null, null, ParameterPrefix);
            return queryData.Select(x => x.TableName).ToList();
        }

        public virtual List<string> FindAllTableNames(DbConnection conn, DbTransaction tran)
        {
            List<string> list = new List<string>();
            //System.Data.DataTable schema = conn.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
            System.Data.DataTable schema = conn.GetSchema("Tables");
            foreach (System.Data.DataRow itemRow in schema.Rows)
            {
                list.Add(itemRow["TABLE_NAME"].ToString());
            }
            return list;
        }
        public virtual string GetIsExistsTableSql(string databaseName, string schemaName)
        {
            string sql = "select Count(1) from sysobjects where xtype='U' AND Name=@Name";
            return sql;
        }
        public virtual bool IsExistsTable<T>(T t, ref string stuffName)
        {
            int hashCode = typeof(T).GetHashCode();
            if (!_dbPks.ContainsKey(hashCode))
            {
                return false;
            }
            List<string> pkFields = _dbPks[hashCode] ?? new List<string>();
            if (pkFields.Count == 0)
            {
                return false;
            }
            string pkId = pkFields[0];
            object objSown = MemberInfoHelper.GetPropertyValue(t, pkId);
            stuffName = DataBaseHelper.CreateTableNameFlag(long.Parse(objSown.ToString()));
            string modelName = typeof(T).Name;
            if (_tablesFields == null || _tablesFields.Count == 0)
            {
                return false;
            }
            string fullTableName = modelName + stuffName;
            bool isExistsTable = _tablesFields.Values.Any(x => x.Any(t => t == fullTableName));

            return isExistsTable;
        }
        protected virtual string GetFullName(string fullTableName)
        {
            //处理表全名
            return fullTableName;
        }
        public virtual bool IsExistsTable(string fullTableName, string schemaName, DbConnection conn, DbTransaction tran)
        {
            //var list = FindAllTableNames(conn, tran);
            //return list.Any(x => x == tableName);
            fullTableName = GetFullName(fullTableName);
            List<string> tempTableNames = new List<string>();
            int hashCode = ConnectionString.GetHashCode();
            if (_tablesFields.ContainsKey(hashCode))
            {
                tempTableNames = _tablesFields[hashCode] ?? new List<string>();
                if (tempTableNames.Any(x => x == fullTableName))
                {
                    Console.WriteLine($"{Thread.CurrentThread.ManagedThreadId}，缓存获取表名：{fullTableName}");
                    return true;
                }

                 DataService.Core.Log.TraceLog.WriteLine($"创建表结构：【{DBType}】【{fullTableName}】，缓存中没有数据，从数据库查查找", "创建表");
            }
            else
            {
                 DataService.Core.Log.TraceLog.WriteLine($"缓存中不存在：【{DBType}】【{fullTableName}】", "创建表");
            }
            if (conn == null)
            {
                return false;
            }

            string sql = GetIsExistsTableSql(conn.DataSource, schemaName);
            Dictionary<string, object> dic = new Dictionary<string, object>();
            dic["Name"] = fullTableName;
            int count = MapperService.ScalarInternal<int>(conn, sql, dic, tran, null, null, ParameterPrefix);
            var stackFrames = StackFrameHelper.GetStackFrame();
            string frameStr = string.Join(">", stackFrames);
             DataService.Core.Log.TraceLog.WriteLine($"【{DBType}】是否存在表结构：【{count}】,线程：{Thread.CurrentThread.ManagedThreadId}，表名：{fullTableName}，执行顺序：{frameStr}", "创建表");
            if (count > 0)
            {
                tempTableNames.Add(fullTableName);
                _tablesFields[hashCode] = tempTableNames;
            }
            return count > 0;

        }

        protected List<ModelColumns> MadeColumnsDatas(DataTable columnData)
        {

            
            var sourceColumnDatas = GetSourceModelColumns();
            bool isExists = columnData.Columns.Contains("CHARACTER_OCTET_LENGTH");
            bool isColumnExists = columnData.Columns.Contains("NUMERIC_PRECISION");
            List<ModelColumns> list = new List<ModelColumns>();
            for (int i = 0; i < columnData.Rows.Count; i++)
            {
                var itemRow = columnData.Rows[i];
                ModelColumns columns = new ModelColumns();
                columns.TableName = itemRow["TABLE_NAME"].ToString();
                int.TryParse(itemRow["ORDINAL_POSITION"].ToString(), out int colOrder);
                columns.ColOrder = colOrder;
                columns.ColName = itemRow["COLUMN_NAME"].ToString();
                columns.ColName = columns.ColName.Replace(" ", "");
                columns.ColType = itemRow["DATA_TYPE"].ToString();
                columns.ColType = columns.ColType.Replace(" ", "");

                int.TryParse(itemRow["CHARACTER_MAXIMUM_LENGTH"].ToString(), out int valueLength);
                columns.ValueLength = valueLength;
                if (isExists)
                {
                    int.TryParse(itemRow["CHARACTER_OCTET_LENGTH"].ToString(), out int colLength);
                    columns.ColLength = colLength;
                }
                else
                {
                    columns.ColLength = columns.ValueLength;
                }
                if (isColumnExists && (nameof(Decimal)).ToLower().Equals(columns.ColType.ToLower()))
                {
                    //decimal类型
                    int.TryParse(itemRow["NUMERIC_PRECISION"].ToString(), out int deciamlValueLength);
                    columns.ValueLength = deciamlValueLength;
                    columns.ColLength = deciamlValueLength;
                }
                else
                {
                    columns.ColLength = columns.ValueLength;
                }

                int.TryParse(itemRow["NUMERIC_SCALE"].ToString(), out int scaleLength);
                columns.ScaleLength = scaleLength;

                columns.IsNullable = itemRow["IS_NULLABLE"].ToString() == "YES" ? true : false;

                columns.DefaultValue = itemRow["COLUMN_DEFAULT"].ToString();

                columns.FieldDes = itemRow["COLUMN_COMMENT"].ToString();
                var fileColumnModel = sourceColumnDatas.Find(x => x.TableName.ToLower() == columns.TableName.ToLower() && x.ColName == columns.ColName);
                if (fileColumnModel != null && string.IsNullOrWhiteSpace(columns.FieldDes))
                {
                    columns.FieldDes = fileColumnModel.FieldDes;
                }
                if (fileColumnModel != null)
                {
                    //mysql 读取表名是小写，为保持实体类名称和数据库名称大小写保持一直，此处重复赋值
                    columns.TableName = fileColumnModel.TableName;
                }
                list.Add(columns);
            }
            return list;
        }
        public virtual List<ModelColumns> FindAllColumns(DbConnection conn)
        {
            DataTable columnData = conn.GetSchema(typeof(ModelColumns).Name);
            List<ModelColumns> list = MadeColumnsDatas(columnData);
            return list;
        }

        public virtual List<ModelIndexs> FindAllIndexs(DbConnection conn)
        {
            List<ModelIndexs> indexsList = new List<ModelIndexs>();
            DataTable indexesColumnData = conn.GetSchema("IndexColumns");
            for (int i = 0; i < indexesColumnData.Rows.Count; i++)
            {
                string tableName = indexesColumnData.Rows[i]["TABLE_NAME"].ToString();
                string index_NAME = indexesColumnData.Rows[i]["INDEX_NAME"].ToString();
                string column_NAME = indexesColumnData.Rows[i]["COLUMN_NAME"].ToString();
                string sort = indexesColumnData.Rows[i]["SORT_MODE"].ToString();

                indexsList.Add(new ModelIndexs
                {
                    TableName = tableName,
                    IndexName = index_NAME,
                    ColumnName = column_NAME,
                    Sort = sort
                }); ;

            }
            DataTable indexesData = conn.GetSchema("Indexes");
            for (int i = 0; i < indexesData.Rows.Count; i++)
            {
                string tableName = indexesData.Rows[i]["TABLE_NAME"].ToString();
                string indexName = indexesData.Rows[i]["INDEX_NAME"].ToString();
                var tempIndexModel = indexsList.Find(x => x.TableName == tableName && x.IndexName == indexName);
                if (tempIndexModel != null)
                {
                    bool ispk = Convert.ToBoolean(indexesData.Rows[i]["PRIMARY_KEY"].ToString());
                    bool isUNIQUE = Convert.ToBoolean(indexesData.Rows[i]["UNIQUE"].ToString());
                    tempIndexModel.PrimaryKey = ispk;
                    tempIndexModel.UQIQUE = isUNIQUE;
                }
            }
            return indexsList;
        }


        protected abstract string GetColType(ModelColumns colDTO);

        private ModelColumns GetNewModelColumns(ModelColumns itemModelColumn)
        {
            var newModelColumn = new ModelColumns();
            newModelColumn.TableName = itemModelColumn.TableName;
            newModelColumn.ColName = itemModelColumn.ColName;
            newModelColumn.Identity = itemModelColumn.Identity;
            string columnType = GetColType(itemModelColumn);
            columnType = columnType.Split('(')[0];
            newModelColumn.ColType = columnType;
            newModelColumn.ValueLength = itemModelColumn.ValueLength;
            newModelColumn.ScaleLength = itemModelColumn.ScaleLength;
            newModelColumn.IsNullable = itemModelColumn.IsNullable;
            newModelColumn.DefaultValue = itemModelColumn.DefaultValue;
            newModelColumn.FieldDes = itemModelColumn.FieldDes;
            return newModelColumn;
        }

        private ModelIndexs GetNewModelIndexs(ModelIndexs itemModelIndex)
        {
            ModelIndexs modelIndexs = new ModelIndexs();
            modelIndexs.TableName = itemModelIndex.TableName;
            modelIndexs.ColumnName = itemModelIndex.ColumnName;
            modelIndexs.PrimaryKey = itemModelIndex.PrimaryKey;
            modelIndexs.UQIQUE = itemModelIndex.UQIQUE;
            modelIndexs.Type_Desc = itemModelIndex.Type_Desc;
            return modelIndexs;

        }
        private Tables GetModelTables(List<ModelColumns> sourceModelColumns, List<ModelIndexs> sourceModelIndexs)
        {
            List<ModelColumns> resultModelColumnss = new List<ModelColumns>();

            List<ModelIndexs> resultModelIndexs = new List<ModelIndexs>();
            foreach (ModelColumns itemModelColumn in sourceModelColumns)
            {
                var newModelColumn = GetNewModelColumns(itemModelColumn);
                resultModelColumnss.Add(newModelColumn);
            }
            foreach (ModelIndexs itemModelIndexs in sourceModelIndexs)
            {
                var newModelColumn = GetNewModelIndexs(itemModelIndexs);
                resultModelIndexs.Add(newModelColumn);
            }
            Tables tables = new Tables()
            {
                Columns = resultModelColumnss,
                Indexs = resultModelIndexs
            };
            return tables;
        }

        public virtual Tables FindAllModelTableSchema(List<string> tableNames)
        {
            List<ModelColumns> sourceModelColumns = GetSourceModelColumns();
            List<ModelIndexs> sourceModelIndexs = MakeSourceIndexs();
            sourceModelColumns = sourceModelColumns.Where(x => tableNames.Any(t => t.ToUpper() == x.TableName.ToUpper())).ToList() ?? new List<ModelColumns>();
            sourceModelIndexs = sourceModelIndexs.Where(x => tableNames.Any(t => t.ToUpper() == x.TableName.ToUpper())).ToList() ?? new List<ModelIndexs>();
            return GetModelTables(sourceModelColumns, sourceModelIndexs);
        }
        public virtual Tables FindAllModelTableSchema()
        {
            List<ModelColumns> sourceModelColumns = GetSourceModelColumns();
            List<ModelIndexs> sourceModelIndexs = MakeSourceIndexs();
            return GetModelTables(sourceModelColumns, sourceModelIndexs);
        }
        public virtual Tables FindAllModelTableSchema(List<string> tableNames, DbConnection conn)
        {
            var columns = FindAllColumns(conn);

            columns = columns.Where(x => tableNames.Contains(x.TableName)).ToList();
            var indexs = FindAllIndexs(conn);
            indexs = indexs.Where(x => tableNames.Contains(x.TableName)).ToList();
            Tables tables = new Tables()
            {
                Columns = columns,
                Indexs = indexs
            };
            return tables;
        }

        public virtual Tables FindAllModelTableSchema(DbConnection conn)
        {
            var columns = FindAllColumns(conn);
            var indexs = FindAllIndexs(conn);
            Tables tables = new Tables()
            {
                Columns = columns,
                Indexs = indexs
            };
            return tables;
        }
        /// <summary>
        /// 创建表架构
        /// </summary>
        /// <param name="dataBaseName">数据库名/表空间</param>

        public virtual bool CreatTableSchema(string modelTableName, string stuff, string dataBaseName, string schemaName, DbConnection conn, DbTransaction tran)
        {
            string fullTableName = modelTableName + stuff;
            string tempDatabaseName = string.IsNullOrEmpty(dataBaseName) ? conn.Database : dataBaseName;
            if (IsExistsTable(fullTableName, schemaName, conn, tran))
            {
                //  DataService.Core.Log.TraceLog.WriteLine($"数据库【{conn.Database}】，表结构【{fullTableName}】已经存在", "创建表架构");
                return true;
            }

            string connectionString = string.Format("Data Source={0};", ModeFilePath);
             DataService.Core.Log.TraceLog.WriteLine($"数据库【{tempDatabaseName}】，创建表结构【{fullTableName}】，模型地址：【{connectionString}】", "创建表架构");
            bool isExistsColumn = !_modelColumns.Any(x => x.TableName == modelTableName);
            if (isExistsColumn)
            {
                using (DatabaseProvider modelProvider = new DataService.DataAccessLayer.SQLiteProvider())
                {
                    modelProvider.ConnectionString = connectionString;
                    //  Expression<Func<ModelColumns, bool>> expression = x => x.TableName == modelTableName;
                    Expression<Func<ModelColumns, bool>> expression = x => true;
                    object paramValue = null;
                    string sql = DBSchemaSqlExtensions.GetSelectSql<ModelColumns>(DBType, expression, ref paramValue, typeof(ModelColumns).Name, null);
                     DataService.Core.Log.TraceLog.WriteLine($"数据库【{tempDatabaseName}】，开始从模型文件读取列和索引，查询列sql语句【{sql}】", "创建表架构");
                    List<ModelColumns> columns = MapperService.QueryInternal<ModelColumns>(modelProvider.DbConnection, sql, paramValue, null, null, null, ParameterPrefix).ToList() ?? new List<ModelColumns>();

                    _modelColumns.AddRange(columns);
                    //Expression<Func<ModelIndexs, bool>> indexExpression = x => x.TableName == modelTableName;
                    Expression<Func<ModelIndexs, bool>> indexExpression = x => true;
                    object indexValue = null;
                    string indexSql = DBSchemaSqlExtensions.GetSelectSql<ModelIndexs>(DBType, indexExpression, ref indexValue, typeof(ModelIndexs).Name, null);
                     DataService.Core.Log.TraceLog.WriteLine($"数据库【{conn.Database}】，开始从模型文件读取列和索引，查询列sql语句【{indexSql}】", "创建表架构");
                    List<ModelIndexs> indexs = MapperService.QueryInternal<ModelIndexs>(modelProvider.DbConnection, indexSql, indexValue, null, null, null, ParameterPrefix).ToList();

                    if (indexs != null && indexs.Count > 0)
                    {
                        _modelIndexs.AddRange(indexs);
                    }
                     DataService.Core.Log.TraceLog.WriteLine($"数据库【{conn.Database}】，创建表结构【{fullTableName}】,开始从模型文件读取列和索引", "创建表架构");

                }
            }
            else
            {
             
            }
            bool isExistsColumnAgain = !_modelColumns.Any(x => x.TableName == modelTableName);
            if (isExistsColumnAgain)
            {
                List<string> modelTablesNames = new List<string>();
                modelTablesNames.Add(modelTableName);
                //新增的表，模型库中不存在，这里尝试 从数据库中取 表结构
                Tables tablesSchems = FindAllModelTableSchema(modelTablesNames, conn);

                List<ModelColumns> columns = tablesSchems.Columns;

                _modelColumns.AddRange(columns);

                 DataService.Core.Log.TraceLog.WriteLine($"数据库【{conn.Database}】，模型文件不存在，从数据库读取索引结构", "创建表架构");
                List<ModelIndexs> indexs = tablesSchems.Indexs;

                if (indexs != null && indexs.Count > 0)
                {
                    _modelIndexs.AddRange(indexs);
                }
                 DataService.Core.Log.TraceLog.WriteLine($"数据库【{conn.Database}】，创建表结构【{fullTableName}】,从数据库读取字段结构和索引结构", "创建表架构");
            }
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            Tables tables = new Tables();

             DataService.Core.Log.TraceLog.WriteLine($"数据库【{conn.Database}】，创建表结构【{fullTableName}】,从静态变量取出列和索引", "创建表架构");
            var tempColumns = _modelColumns.Where(x => x.TableName == modelTableName).ToList();
            foreach (var item in tempColumns)
            {
                item.FullTableName = fullTableName;
                item.DataBaseName = dataBaseName;
            }
            var tempIndexs = _modelIndexs.Where(x => x.TableName == modelTableName).ToList();
            foreach (var item in tempIndexs)
            {
                item.FullTableName = fullTableName;
                item.DataBaseName = dataBaseName;
            }

            tables.Columns = tempColumns;

            tables.Indexs = tempIndexs;

            CreatTable(tables, conn, tran);

            stopwatch.Stop();
            Console.WriteLine($"创建{fullTableName}完成，耗时{stopwatch.ElapsedMilliseconds}");
            return true;


        }
        /// <summary> 修正表字段、索引、字段类型、长度等 </summary>
        //public bool CorrectTable(DbConnection conn)
        //{

        //}
        public abstract bool CreatTable(Tables tables, DbConnection conn, DbTransaction tran);


        public abstract bool CreateDataBaseSchema(string dbFileDir, string databaseName, DbConnection conn);


        public virtual void CreateModelFile(Tables tables, DbConnection conn, DbTransaction tran)
        {
        }

        public virtual void CreateModelSchema(DbConnection conn, DbTransaction tran)
        {

        }

        public virtual bool FullDBInitData(List<ModelInitData> modelInitDatas, DbConnection conn, DbTransaction tran)
        {
            List<string> sqls = modelInitDatas.Select(x => x.DataFields).ToList();
            MapperService.ExecuteSchemaSql(conn, tran, sqls, ParameterPrefix);
            return true;
        }

        #region 创建分词
        //        public virtual string AnalysisSql
        //        {
        //            get
        //            {
        //                return @"CREATE TABLE Columns (
        //    Id           BIGINT Not Null PRIMARY KEY,
        //    TableName    VARCHAR (100),
        //    ColOrder     INT,
        //    ColName      VARCHAR (50),
        //    Flag         BOOLEAN,
        //    ColType      VARCHAR (30),
        //    ColLength    INT,
        //    ValueLength  INT,
        //    ScaleLength  INT,
        //    IsNullable   BOOLEAN,
        //    DefaultValue VARCHAR (100),
        //    FieldDes     VARCHAR (100),
        //FullTableName VARCHAR (100)
        //);";
        //            }
        //        }
        //        /// <summary>
        //        /// 创建分词表
        //        /// </summary>
        //        public virtual bool CreateAnalysisTable(DbConnection conn, DbTransaction tran)
        //        {
        //            adsfdsf

        //        }
        #endregion

        protected int LockMethod<T1, T2, T3, T4>(Func<T1, T2, T3, T4, int> func, T1 t1, T2 t2, T3 t3, T4 t4)
        {
            bool acquiredLock = false;
            try
            {
                Monitor.TryEnter(_lock, _lockTimeOut, ref acquiredLock);
                if (acquiredLock)
                {
                    int count = func(t1, t2, t3, t4);
                    return count;
                }
                else
                {
                    var stackFrames = StackFrameHelper.GetStackFrame();
                    string frameStr = string.Join(">", stackFrames);
                     DataService.Core.Log.TraceLog.WriteLine($"【{DBType}】线程：{Thread.CurrentThread.ManagedThreadId}，获取锁资源失败，没有抢到锁，调用顺序：{frameStr}，时间：{DateTime.Now}", "数据库执行增删改");
                    return 0;
                    // _lockTimeOut时间内,没有抢到锁
                }
            }
            finally
            {
                if (acquiredLock)
                {
                    Monitor.Exit(_lock);
                }
            }
        }


        protected int LockMethod<T1, T2, T3, T4, T5>(Func<T1, T2, T3, T4, T5, int> func, T1 t1, T2 t2, T3 t3, T4 t4, T5 t5)
        {
            bool acquiredLock = false;
            try
            {
                Monitor.TryEnter(_lock, _lockTimeOut, ref acquiredLock);
                if (acquiredLock)
                {
                    int count = func(t1, t2, t3, t4, t5);
                    return count;
                }
                else
                {
                    var stackFrames = StackFrameHelper.GetStackFrame();
                    string frameStr = string.Join(">", stackFrames);
                     DataService.Core.Log.TraceLog.WriteLine($"【{DBType}】线程：{Thread.CurrentThread.ManagedThreadId}，获取锁资源失败，没有抢到锁，调用顺序：{frameStr}，时间：{DateTime.Now}", "数据库执行增删改");
                    return 0;
                    // _lockTimeOut时间内,没有抢到锁
                }
            }
            finally
            {
                if (acquiredLock)
                {
                    Monitor.Exit(_lock);
                }
            }
        }

    }
}
